package com.crm.dao;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.swing.JOptionPane;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;


import com.crm.entity.Opportunity;
import com.crm.entity.Parts;
import com.crm.util.ApplicationContext;

public class PartsDAO {
	protected final Log logger = LogFactory.getLog(getClass());
	
	public  int insert(Parts cl)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		   tx = session.beginTransaction();
		     session.saveOrUpdate(cl);
		    tx.commit();
		     return 0;
		}
		catch (Exception e) {
		   if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  int update(Parts c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		    tx = session.beginTransaction();
		     session.saveOrUpdate(c);
		    tx.commit();
		     return 0;
		}
		catch (Exception e) {
		   if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  int delete(Parts c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		    tx = session.beginTransaction();
		     session.delete(c);
		    tx.commit();
		     return 0;
		}
		catch (Exception e) {
		  if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}

	public  List<Parts> findAll(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try { 
		     List<Parts> custObjs=new ArrayList<Parts>();
		     List objs=null;
		     Query query=session.createQuery("from Parts where business_id=:busid");
		     query.setInteger("busid", busid);
		     objs=query.list();
		     for(Object o:objs){
		    	 custObjs.add((Parts)o);
		     }
		     return custObjs;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Parts findById(int partid,int busid,int enqid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qrystr="From Parts where partid=:pid and business_id=:busid and enqid=:enqid ";
		     Query qry=session.createQuery(qrystr);
		     qry.setInteger("pid", partid);
		     qry.setInteger("busid", busid);
		     qry.setInteger("enqid",enqid);
		     Object ob=qry.uniqueResult();
		     Parts p=(Parts)ob;
		     return p;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Parts findByPartStock(int partid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qrystr="From Parts where partid=:pid and business_id=:busid ";
		     Query qry=session.createQuery(qrystr);
		     qry.setInteger("pid", partid);
		     qry.setInteger("busid", busid);
		     Object ob=qry.uniqueResult();
		     Parts p=(Parts)ob;
		     return p;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}

	public  Parts getByName(int enqid,String name,int busid) throws Exception {
	
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Parts where enqid=:enqid and prtnum=:name or description=:name and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("enqid", enqid);
		     query.setString("name", name);
		     query.setInteger("busid", busid);
		     Object queryresult=query.uniqueResult();
		     Parts comp=(Parts)queryresult;
		     return comp;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Parts getById(int enqid,int id,int busid) throws Exception {
		
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Parts where enqid=:enqid and partid=:id and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("enqid", enqid);
		     query.setInteger("id",id);
		     query.setInteger("busid", busid);
		     Object queryresult=query.uniqueResult();
		     Parts comp=(Parts)queryresult;
		     return comp;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Parts getByNameDesc(int enqid,String name,String desc,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Parts where enqid=:enqid and prtnum=:name and description=:desc and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("enqid", enqid);
		     query.setString("name", name);
		     query.setString("desc", desc);
		     query.setInteger("busid", busid);
		     Object queryresult=query.uniqueResult();
		     Parts comp=(Parts)queryresult;
			return comp;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Parts> getParts(String ref,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			System.out.println("In get Parts ");
		     String querystr="from Parts  where enqid in(select id from Opportunity where ref_num=:ref and business_id=:busid) and business_id=:busid order by partid";
		     Query qury=session.createQuery(querystr);
		     qury.setString("ref", ref);
		     qury.setInteger("busid", busid);
		    
			List<Parts>  parts=new ArrayList<Parts>();
			List<Object> res=qury.list();
			if(res!=null)
				for(Object o:res){
					parts.add((Parts)o);
				}
			return parts;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Parts> getPartsByIds(int busid,int refid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qs="from Parts where business_id=:busid and enqid=:refid";
		     Query qr=session.createQuery(qs);
		     qr.setInteger("refid", refid);
		     qr.setInteger("busid",busid);
		     List<Parts>  parts=new ArrayList<Parts>();
		     List<Object> res=qr.list();
		     if(res!=null)
		    	 for(Object o:res){
		    		 parts.add((Parts)o);
		    	 }
		     return parts;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public  Parts findByPartIdEnqId(int enqid,int partid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qrystr="From Parts ps where ps.partid=:pid and ps.enqid=:eid and business_id=:busid";
		     Query qr=session.createQuery(qrystr);
		     qr.setInteger("pid", partid);
		     qr.setInteger("eid", enqid);
		     qr.setInteger("busid", busid);
		     Object ob=qr.uniqueResult();
		     Parts pr=(Parts)ob;
		     return pr;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Parts findByPartNameEnqId(int enqid, String prtnum,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qrystr="From Parts ps where ps.prtnum=:partnum and ps.enqid=:eid and business_id=:busid";
		     Query qr=session.createQuery(qrystr);
		     qr.setString("partnum", prtnum);
		     qr.setInteger("eid", enqid);
		     qr.setInteger("busid", busid);
			Object ob=qr.uniqueResult();
			Parts pr=(Parts)ob;
			return pr;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Parts findByPartNameDescEnqId(int enqid, int prtid,String desc,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qrystr="From Parts ps where ps.partid=:id and description=:desc and ps.enqid=:eid and business_id=:busid";
		     Query qr=session.createQuery(qrystr);
		     qr.setInteger("id",prtid);
		     qr.setInteger("eid", enqid);
		     qr.setInteger("busid", busid);
		     qr.setString("desc", desc);
		     Object ob=qr.uniqueResult();
		     Parts pr=(Parts)ob;
		     return pr;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Parts findStock(int partid, String prtnum,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qrystr="From Parts ps where ps.partid=:partid and ps.prtnum like '%"+prtnum+"%' and business_id=:busid";
		     Query qr=session.createQuery(qrystr);
		     qr.setInteger("partid", partid);
		     qr.setInteger("busid", busid);
		     Object ob=qr.uniqueResult();
		     Parts pr=(Parts)ob;
		     return pr;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Parts findAllStock(int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qrystr="From Parts ps where business_id=:busid";
		     Query qr=session.createQuery(qrystr);
		     qr.setInteger("busid", busid);
		     Object ob=qr.uniqueResult();
		     Parts pr=(Parts)ob;
		     return pr;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public void deleteParts(int busId, int opid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction(); 
		     String qrystr="delete From Parts where business_id=:busid and enqid=:opid";
		     Query qr=session.createQuery(qrystr);
		     qr.setInteger("busid", busId);
		     qr.setInteger("opid", opid);
		     qr.executeUpdate();
		     tx.commit();
			 return ;
		}
		catch (Exception e) {
		     if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public  Parts getPrtId(String refNo, String partNumber,String desc, int enqid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			System.out.println("In getPrtId");
			String querystr="from Parts p where p.enqid=(select id from Opportunity where ref_num=:refnum and business_id=:busid) and p.prtnum=:prt and p.description=:desc and enqid=:enqid and business_id=:busid";
			Query qury=session.createQuery(querystr);
			qury.setString("refnum", refNo);
			qury.setString("desc", desc);
			qury.setInteger("busid", busid);
			qury.setInteger("busid", busid);
			qury.setString("prt", partNumber);
			qury.setInteger("enqid", enqid);
			Object qrslt=qury.uniqueResult();
			Parts p=(Parts)qrslt;
			return p;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Parts getByPart(String partNumber,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			System.out.println("In getPrtId");
			String querystr="from Parts p where p.prtnum like '%"+partNumber+"%' and business_id=:busid";
			Query qury=session.createQuery(querystr);
			qury.setInteger("busid", busid);
			Object qrslt=qury.uniqueResult();
			Parts p=(Parts)qrslt;
			return p;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
public List<Parts> findByPartName(String partname, int busid) throws HibernateException, Exception {
	Session session =  ApplicationContext.getHiberSession().openSession();
	try {
		System.out.println("In getPrtId");
		String querystr="from Parts p where p.prtnum like '%"+partname+"%' and business_id=:busid and p.prtnum is not null and enqid is not null";
		Query qury=session.createQuery(querystr);
		qury.setInteger("busid", busid);
		Object qrslt=qury.list();
		List<Parts> p=(List<Parts>)qrslt;
		return p;
	}
	catch (Exception e) {
		throw e;
	}
	finally {
		session.close();
	}
		
	}
	/*
	public  List<Parts> getParts(String ref,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			List<Parts> prtlist=new ArrayList<Parts>();
			String querystr="from Opportunity where ref_num=:ref and business_id=:busid";
			Query qury=session.createQuery(querystr);
			qury.setString("ref", ref);
			qury.setInteger("busid", busid);
			Object queryresult=qury.uniqueResult();
			Opportunity op=(Opportunity)queryresult;
			int pid=op.getId();
			String qs="from Parts where enqid=:pid and business_id=:busid ORDER BY  id";
			Query qr=session.createQuery(qs);
			qr.setInteger("pid", pid);
			qr.setInteger("busid", busid);
			List<Object> p=qr.list();
		
			Iterator itr=p.iterator();
			while(itr.hasNext()==true){
				Object ob=itr.next();
				Parts prts=(Parts)ob;
				prtlist.add(prts);
			}
			return prtlist;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}*/
public Parts findByPartId(int partId, int busid) throws HibernateException, Exception {
	Session session =  ApplicationContext.getHiberSession().openSession();
	try {
		System.out.println("In getPrtId");
		String querystr="from Parts p where p.partid=:partId and business_id=:busid";
		Query qury=session.createQuery(querystr);
		qury.setInteger("busid", busid);
		qury.setInteger("partId", partId);
		Object qrslt=qury.uniqueResult();
		Parts p=(Parts)qrslt;
		return p;
	}
	catch (Exception e) {
		throw e;
	}
	finally {
		session.close();
	}
}
/*public List<Integer> findStockByName(String partName, int businessId) throws HibernateException, Exception {
	Session session =  ApplicationContext.getHiberSession().openSession();
	try {
		System.out.println("In getPrtId");
		String querystr="select partid from Parts p where p.prtnum=:partName and business_id=:busid ";
		Query qury=session.createQuery(querystr);
		qury.setInteger("busid", businessId);
		qury.setString("partName", partName);
		Object qrslt=qury.list();
		List<Integer> p=(List<Integer>)qrslt;
		return p;
	}
	catch (Exception e) {
		throw e;
	}
	finally {
		session.close();
	}
}*/
public Parts findByPart(String partName, int businessId) throws HibernateException, Exception {
	Session session =  ApplicationContext.getHiberSession().openSession();
	try {
		System.out.println("In getPrtId");
		String querystr="from Parts where prtnum=:partName and business_id=:busid";
		Query qury=session.createQuery(querystr);
		qury.setInteger("busid", businessId);
		qury.setString("partName", partName);
		qury.setMaxResults(1);
		Object qrslt=qury.uniqueResult();
		Parts p=(Parts)qrslt;
		return p;
	}
	catch (Exception e) {
		throw e;
	}
	finally {
		session.close();
	}
	
}
	
	
	/*public  List<Object> parts(int id,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Parts  where enqid=:enqnum and business_id=:busid";
		     logger.info(querystr);
		     Query query=session.createQuery(querystr);
		     query.setInteger("enqnum", id);
		     query.setInteger("busid", busid);
		     Object queryresult=query.list();
		     List<Object> p=(List<Object>) queryresult;
		     return p;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}

	}*/
	
	
	/*public  List<Object> getParts(String enqnumb,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qstr="From Parts p where p.enqNo=:enqnumb and business_id=:busid";
		     Query qr=session.createQuery(qstr);
		     qr.setString("enqnumb",enqnumb); 
		     qr.setInteger("busid", busid);
		     List<Object> qresult=qr.list();
		     return qresult;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}*/
}
